﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.IO;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.Windows.Forms;
using CoursePack;

namespace TheSchedulator
{
    public static class MySQLApi
    {
        private static MySqlConnection GetConnection()
        {
            return new MySqlConnection("server=concord.host-ed.me; database=concordh_con; uid=concordh; pwd=F6owt57kQ7");
        }

        /// <summary>
        /// We verify the student login information against the database to see if any matches
        /// </summary>
        /// <param name="_UserName"></param>
        /// <param name="_PassWord"></param>
        /// <returns></returns>
        public static Student VerifyLogin(string _UserName, string _PassWord)
        {
            Student user = null;

            string sql = "SELECT * FROM  Students WHERE user_name = ?user_name AND password = ?password";
            MySqlConnection conn = GetConnection();

            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("user_name", _UserName);
            command.Parameters.AddWithValue("password", _PassWord);
            
           

                conn.Open();
                
                MySqlDataReader rdr = command.ExecuteReader();
                
                if (rdr.HasRows)
                {

                    rdr.Read();
                    byte[] me = (byte[])rdr["data"];

                    if (me == null)
                    {
                        return null;
                    }
                    else
                    {
                        if (me.Length == 0)
                        {
                            return null;
                        }
                        else
                        {
                            BinaryFormatter formatter = new BinaryFormatter();
                            MemoryStream ms = new MemoryStream(me);
                            ms.Position = 0;
                            return (Student)formatter.Deserialize(ms);
                        }
                    }
                }
            /*
            catch
            {
                MessageBox.Show("An error has occured in the database. Please try again !");
            }
        */

            return null;
        }

        /// <summary>
        /// The student is registred in the database
        /// </summary>
        /// <param name="_Username"></param>
        /// <param name="_Password"></param>
        /// <param name="_Student"></param>
        /// <returns></returns>
        public static bool RegisterStudentDatabase(string _Username, string _Password, Student _Student)
        {
            bool bIsInserted = false;

            //Serialisation of the student object
            MemoryStream oStream = new MemoryStream();
            BinaryFormatter oBF = new BinaryFormatter();
            oBF.Serialize(oStream, _Student);

            // Transfer the serialisation into an array of byte
            byte[] arrBuffer = new byte[oStream.Length];
            oStream.Seek(0, SeekOrigin.Begin);
            oStream.Read(arrBuffer, 0, arrBuffer.Length);
            oStream.Close();

            MySqlConnection conn = GetConnection();

            // We create a MySQL command to insert a new record
            MySqlCommand oCmd = new MySqlCommand("SELECT * FROM " + "Students" + " WHERE " + "first_name" + " = ?" + "first_name", conn);

            oCmd.Parameters.AddWithValue("first_name", _Student.FirstName);
            oCmd.Parameters.AddWithValue("last_name", _Student.LastName);
            oCmd.Parameters.AddWithValue("user_name", _Username);
            oCmd.Parameters.AddWithValue("password", _Password);


            MySqlDataAdapter oDataA = new MySqlDataAdapter(oCmd);
            MySqlCommandBuilder oCmdBuilder = new MySqlCommandBuilder(oDataA);

            DataTable oDataT = new DataTable();

            oDataA.Fill(oDataT);

            // We set the users information to insert into the database
            if (oDataT.Rows.Count == 0)
            {
                DataRow oDataRow = oDataT.NewRow();

                oDataRow["first_name"] = _Student.FirstName;
                oDataRow["last_name"] = _Student.LastName;
                oDataRow["user_name"] = _Username;
                oDataRow["password"] = _Password;

                oDataRow["data"] = arrBuffer;
                oDataT.Rows.Add(oDataRow);

                // If an error is thrown it is likely to be a duplicate entry
                // Otherwise the database is unavailable or the user is not connected to internet
                try
                {
                    oDataA.Update(oDataT);
                }
                catch
                {
                    MessageBox.Show("Error ! You have already registered ! ");
                }
                bIsInserted = true;
            }


            return bIsInserted;
        }

        public static CourseSequence GetCourseSequence(EnumDegree _EnumDegree)
        {
            string sql = "SELECT * FROM  CourseSequences WHERE Degree = ?Degree";
            MySqlConnection conn = GetConnection();

            MySqlCommand command = new MySqlCommand(sql, conn);
            command.Parameters.AddWithValue("degree", _EnumDegree.ToString());
            try
            {
                conn.Open();
            }
            catch
            {
                MessageBox.Show("The connection to the database has failed. Make sure you are connected to Internet.");
            }

            MySqlDataReader rdr = command.ExecuteReader();

            if (rdr.HasRows)
            {
                rdr.Read();
                byte[] me = (byte[])rdr["data"];

                if (me == null)
                {
                    return null;
                }
                else
                {
                    if (me.Length == 0)
                    {
                        return null;
                    }
                    else
                    {
                        BinaryFormatter formatter = new BinaryFormatter();
                        MemoryStream ms = new MemoryStream(me);
                        return (CourseSequence)formatter.Deserialize(ms);
                    }
                }
            }
            return null;
        }

        public static List<Course> GetCourses(Session _Session, List<Department> _Departments)
        {
            List<Course> courses = new List<Course>();
            MySqlConnection conn = GetConnection();


            foreach (Department _Department in _Departments)
            {
                string sql = "SELECT * FROM " + _Department.Id + "_Courses" + _Session.Year + "_" + _Session.ESession;


                try
                {
                    conn.Open();
                }
                catch
                {
                    MessageBox.Show("The connection to the database has failed. Make sure you are connected to Internet.");
                    return null;
                }

                MySqlCommand command = new MySqlCommand(sql, conn);             

                try
                {
                    MySqlDataReader rdr = command.ExecuteReader();

                    Course course;

                    while (rdr.Read())
                    {
                        Class Lecture = new Class(ClassType.Lecture, rdr.GetString("LectDays"), rdr.GetString("LectStart"), rdr.GetString("LectEnd"), rdr.GetString("LectSect"), rdr.GetString("LectClass"), rdr.GetString("LectProf"));
                        Class Tutorial = null, Lab = null;

                        if (rdr.GetString("TutDays") != "")
                        {
                            Tutorial = new Class(ClassType.Tutorial, rdr.GetString("TutDays"), rdr.GetString("TutStart"), rdr.GetString("TutEnd"), rdr.GetString("TutSect"), rdr.GetString("TutClass"));
                        }

                        if (rdr.GetString("LabDays") != "")
                        {
                            Lab = new Class(ClassType.Lab, rdr.GetString("LabDays"), rdr.GetString("LabStart"), rdr.GetString("LabEnd"), rdr.GetString("LabSect"), rdr.GetString("LabClass"));
                        }

                        course = new Course(
                            rdr.GetString("CourseName"),
                            rdr.GetString("CourseNumber"),
                            rdr.GetString("CourseTitle"),
                            Lecture, Tutorial, Lab,
                            rdr.GetString("Credits"),
                            rdr.GetString("Session"),
                            rdr.GetString("Prerequisite"),
                            rdr.GetString("SpecialNote"),
                            CourseStatus.NotStarted);

                        courses.Add(course);
                    }

                    conn.Close();
                }
                catch
                {
                    MessageBox.Show("The requested courses are unavailable at the moment !");
                }

            }

            return courses;
        }

        public static bool UpdateCourseSequence(EnumDegree _EnumDegree, CourseSequence _CourseSequence)
        {
            //Serialisation of the student object
            MemoryStream oStream = new MemoryStream();
            BinaryFormatter oBF = new BinaryFormatter();
            oBF.Serialize(oStream, _CourseSequence);

            // Transfer the serialisation into an array of byte
            byte[] arrBuffer = new byte[oStream.Length];
            oStream.Seek(0, SeekOrigin.Begin);
            oStream.Read(arrBuffer, 0, arrBuffer.Length);
            oStream.Close();

            MySqlConnection conn = GetConnection();

            // We create a MySQL command to insert a new record
            MySqlCommand oCmd = new MySqlCommand("SELECT * FROM CourseSequences WHERE Degree" + " = ?" + "Degree", conn);

            oCmd.Parameters.AddWithValue("Degree", _EnumDegree.ToString());
            MySqlDataAdapter oDataA = new MySqlDataAdapter(oCmd);
            MySqlCommandBuilder oCmdBuilder = new MySqlCommandBuilder(oDataA);

            DataTable oDataT = new DataTable();

            oDataA.Fill(oDataT);

            // We set the users information to insert into the database
            if (oDataT.Rows.Count == 0)
            {
                DataRow oDataRow = oDataT.NewRow();

                oDataRow["Degree"] = _EnumDegree.ToString();

                oDataRow["data"] = arrBuffer;
                oDataT.Rows.Add(oDataRow);

                // If an error is thrown it is likely to be a duplicate entry
                // Otherwise the database is unavailable or the user is not connected to internet
                try
                {
                    oDataA.Update(oDataT);
                }
                catch
                {
                    MessageBox.Show("Error !");
                }
            }

            return true;
        }

        public static bool UpdateCourses(Department _Dep, DataTable _Data, Session _Session)
        {
            MySqlConnection conn = GetConnection();

            if (!HasTable(_Dep.Id + "_Courses" + _Session.Year + "_" + _Session.ESession))
            {
                MySqlCommand oCmd = new MySqlCommand("CREATE TABLE "
                + _Dep.Id + "_Courses" + _Session.Year + "_" + _Session.ESession + "("
                + "CourseName VARCHAR(10),"
                + "CourseNumber VARCHAR(10),"
                + "CourseTitle VARCHAR(255),"
                + "LectDays VARCHAR(20),"
                + "LectStart VARCHAR(10),"
                + "LectEnd VARCHAR(10),"
                + "LectSect VARCHAR(10),"
                + "LectClass VARCHAR(50),"
                + "LectProf VARCHAR(255),"
                + "TutDays VARCHAR(20),"
                + "TutStart VARCHAR(10),"
                + "TutEnd VARCHAR(10),"
                + "TutSect VARCHAR(10),"
                + "TutClass VARCHAR(50),"
                + "LabDays VARCHAR(20),"
                + "LabStart VARCHAR(10),"
                + "LabEnd VARCHAR(10),"
                + "LabSect VARCHAR(10),"
                + "LabClass VARCHAR(50),"
                + "Credits VARCHAR(10),"
                + "Session VARCHAR(10),"
                + "Prerequisite VARCHAR(500),"
                + "SpecialNote VARCHAR(500))", conn);

                conn.Open();

                oCmd.ExecuteNonQuery();

                MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter("SELECT * from " + _Dep.Id + "_Courses" + _Session.Year + "_" + _Session.ESession, conn);
                MySqlCommandBuilder cmb = new MySqlCommandBuilder(mySqlDataAdapter);

                mySqlDataAdapter.Fill(_Data);

                mySqlDataAdapter.Update(_Data);
                return true;
            }

            return false;

            
        }

        private static bool HasTable(string _TableName)
        {

            string cmdStr = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'concordh_con' AND table_name = " + "'" + _TableName + "'";

            using ( MySqlConnection conn = GetConnection())
            {
                MySqlCommand cmd = new MySqlCommand(cmdStr, conn);
                conn.Open();

                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    int count = reader.GetInt32(0);
                    if (count == 0)
                    {
                        return false;
                    }
                    else if (count == 1)
                    {
                        return true;
                    }
                }
            }

            return false;
        }

        public static bool SaveStudent(Student _Student)
        {
            bool bIsInserted = false;

            //Serialisation of the student object
            MemoryStream oStream = new MemoryStream();
            BinaryFormatter oBF = new BinaryFormatter();
            oBF.Serialize(oStream, _Student);

            // Transfer the serialisation into an array of byte
            byte[] oSerializedImg = oStream.ToArray();

            MySqlConnection conn = GetConnection();

            // We create a MySQL command to insert a new record
            MySqlCommand oCmd = new MySqlCommand("SELECT * FROM " + "Students" + " WHERE " + "user_name" + " = ?user_name", conn);

            oCmd.Parameters.AddWithValue("user_name", _Student.UserName);


            MySqlDataAdapter oDataA = new MySqlDataAdapter(oCmd);
            MySqlCommandBuilder oCmdBuilder = new MySqlCommandBuilder(oDataA);

            DataTable oDataT = new DataTable();

            oDataA.Fill(oDataT);

            // We set the users information to insert into the database
            if (oDataT.Rows.Count > 0)
            {

                oDataT.Rows[0]["data"] = oSerializedImg;

                // If an error is thrown it is likely to be a duplicate entry
                // Otherwise the database is unavailable or the user is not connected to internet
                try
                {
                    oDataA.Update(oDataT);
                }
                catch
                {
                    MessageBox.Show("Error ! ");
                }
                bIsInserted = true;
            }


            return bIsInserted;
        }
    }
}
